import tkinter as tk
from tkinter import ttk, messagebox
import mysql.connector
import json
import datetime
import random
import requests

# =================== 安徽英彩数据库配置 =================== #
DB_CONFIG = {
    "host": "192.168.32.116",
    "port": 3306,
    "user": "root",
    "password": "gjvzgQVxj1pfxzkpF0FW2OwK",
    "database": "tms_v2"
}

HAIKANG_URL = "http://192.168.32.116:9999/jeecg-tms-api/tms/api/agv/agvCallback"
WEILAI_URL = "";
# =================== 安徽英彩数据库配置 =================== #




# =================== 数据库连接封装 =================== #
def get_db_connection():
    return mysql.connector.connect(**DB_CONFIG)
# =================== 数据库连接封装 =================== #





# =================== 通用输出控件封装 =================== #
def create_output_with_scrollbar(parent, row, columnspan):
    frame = tk.Frame(parent)
    frame.grid(row=row, column=0, columnspan=columnspan, pady=10, sticky="nsew")

    frame.rowconfigure(0, weight=1)
    frame.columnconfigure(0, weight=1)

    output = tk.Text(frame, state="disabled", wrap="none")
    output.grid(row=0, column=0, sticky="nsew")

    scrollbar = tk.Scrollbar(frame, command=output.yview)
    scrollbar.grid(row=0, column=1, sticky="ns")
    output.config(yscrollcommand=scrollbar.set)

    return output

def log(output_widget, msg):
    timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    output_widget.config(state="normal")
    output_widget.insert(tk.END, f"\n[{timestamp}] {msg}")
    output_widget.config(state="disabled")
    output_widget.update()
# =================== 通用输出控件封装 =================== #


# =================== 业务功能函数 > 随机请求Code生成 =================== #
def generate_id():
    return ''.join([str(random.randint(0, 9)) for _ in range(19)])
# =================== 业务功能函数 > 随机请求Code生成 =================== #


# =================== 业务功能函数 > 查询容器池任务 =================== #
def query_task(task_id, output_widget):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True, buffered=True)

    # 根据任务组编码查询任务
    sql = """
        SELECT 
            tp.id as 'ID',
            tp.task_code as 'TMS容器任务号',
            tp.pid_code as '父任务号',
            t.task_code as 'RCS车辆任务号',
            tp.issue_time as '下发时间',
            tp.task_over_time as '任务完成时间',
            sls.storage_location_code as '起点编码',
            sle.storage_location_code as '终点编码',
            c.container_code as '容器编码',
            fc.flow_card_number as '流程卡号'
        FROM 
            tms_task_poll tp left join tms_storage_location sls on tp.start_location_id = sls.id
            left join tms_storage_location sle on tp.end_location_id = sle.id
            left join tms_container c on tp.container_id = c.id
            left join tms_flow_card fc on c.id = fc.container_id
            left join tms_task t on t.task_poll_id = tp.id
        WHERE tp.pid_code = %s
    """
    cursor.execute(sql, (task_id,))
    result = cursor.fetchone()

    output_widget.config(state="normal")
    output_widget.delete("1.0", tk.END)

    if not result:
        output_widget.insert(tk.END, "未找到该任务")
        output_widget.config(state="disabled")
        conn.close()
        return

    # if result.get('父任务号') == '0':
    #     output_widget.insert(tk.END, "该任务是任务组, 请查询TMS容器任务")
    #     output_widget.config(state="disabled")
    #     conn.close()
    #     return

    # 查询任务状态记录时间轴
    status_sql = """
        SELECT
            CASE status
                WHEN 10 THEN '创建'
                WHEN 20 THEN '待执行'
                WHEN 30 THEN '执行中'
                WHEN 40 THEN '已离开'
                WHEN 50 THEN '已完成'
                WHEN 60 THEN '已取消'
                ELSE CONCAT('未知状态(', status, ')')
            END AS '任务状态',
            create_time AS '创建时间'
        FROM tms_task_poll_status
        WHERE task_poll_id =  %s
        order by create_time ASC
    """
    cursor.execute(status_sql, (result.get('ID'),))
    task_status_list = cursor.fetchall()

    formatted_result = json.dumps(result, indent=4, ensure_ascii=False, default=str)
    output_widget.insert(tk.END, formatted_result)
    output_widget.insert(tk.END, '\n---------- 任务记录 ----------\n')
    formatted_task_status_list = json.dumps(task_status_list, indent=4, ensure_ascii=False, default=str)
    output_widget.insert(tk.END, formatted_task_status_list)

    output_widget.config(state="disabled")
    conn.close()
# =================== 业务功能函数 > 查询容器池任务 =================== #

# =================== 业务功能函数 > 取消任务 =================== #
def cancel_task(task_id, output_widget):
    def log(msg):
        output_widget.config(state="normal")
        output_widget.insert(tk.END, "\n" + msg)
        output_widget.config(state="disabled")
        output_widget.update()

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("SELECT * FROM tms_task_poll WHERE pid_code = %s", (task_id,))
    task = cursor.fetchone()
    if not task:
        log(f"未找到任务号为 {task_id} 的容器任务")
        conn.close()
        return

    status = int(task['status'])
    if status == 40:
        log("任务数据已离开状态，请选择完成任务或手动修改")
        conn.close()
        return
    elif status in (50, 60):
        log("任务数据已完成或已取消，无需修改")
        conn.close()
        return

    pid_code = task['pid_code']
    task_poll_id = task['id']
    update_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    update_by = '任务取消工具'

    cursor.execute("SELECT * FROM tms_task_poll WHERE task_code = %s", (pid_code,))
    parent_task = cursor.fetchone()

    cursor.execute("SELECT * FROM tms_task WHERE task_poll_id = %s", (task_poll_id,))
    car_task = cursor.fetchone()

    cursor.execute("UPDATE tms_task_poll SET status = 60, update_time = %s, update_by = %s WHERE id = %s",
                   (update_time, update_by, task_poll_id))
    if cursor.rowcount > 0:
        log("修改容器任务状态为‘取消’成功")
    else:
        log(f"警告：未找到对应任务 ID={task_poll_id}，状态未更新")

    if parent_task:
        cursor.execute("UPDATE tms_task_poll SET status = 60, group_status = 60, update_time = %s, update_by = %s WHERE id = %s",
                       (update_time, update_by, parent_task['id']))
    if cursor.rowcount > 0:
        log("修改容器任务组状态为‘取消’成功")
    else:
        log(f"警告：未找到对应任务 ID={task_poll_id}，状态未更新")

    if car_task:
        cursor.execute("UPDATE tms_task SET status = 60, update_time = %s, update_by = %s WHERE id = %s",
                       (update_time, update_by, car_task['id']))
    if cursor.rowcount > 0:
        log("修改车辆任务状态为‘取消’成功")
    else:
        log(f"警告：未找到对应任务 ID={task_poll_id}，状态未更新")

    status_record_id = generate_id()
    cursor.execute("INSERT INTO tms_task_poll_status (id, task_poll_id, status, create_by, create_time) VALUES (%s, %s, 60, %s, %s)",
                   (status_record_id, task_poll_id, update_by, update_time))
    if cursor.rowcount > 0:
        log("新增容器任务记录‘取消’成功")
    else:
        log(f"警告：新增对应任务 ID={task_poll_id} 任务记录失败")

    if task['start_location_id']:
        cursor.execute("UPDATE tms_storage_location SET storage_location_status = 0 WHERE id = %s", (task['start_location_id'],))
        if cursor.rowcount > 0:
            log("任务起点状态重置为“有货”成功")
        else:
            log(f"警告：未找到对应任务 ID={task_poll_id}，任务起点状态未重置")

    if task['end_location_id']:
        cursor.execute("UPDATE tms_storage_location SET storage_location_status = 1 WHERE id = %s", (task['end_location_id'],))
        if cursor.rowcount > 0:
            log("任务终点状态重置为“空闲”成功")
        else:
            log(f"警告：未找到对应任务 ID={task_poll_id}，任务终点状态未重置")

    if task['container_id']:
        cursor.execute("UPDATE tms_container SET business_status = 0, deleted = 0 WHERE id = %s", (task['container_id'],))
        if cursor.rowcount > 0:
            log("任务容器业务状态重置为“可用”成功")
        else:
            log(f"警告：未找到对应任务 ID={task_poll_id}，任务容器业务状态未重置")

    conn.commit()
    conn.close()
    log(f"任务 {task_id} 取消操作完成。")
# =================== 业务功能函数 > 取消任务 =================== #


# =================== 业务功能函数 > 海康完成任务逻辑 =================== #
def call_haikang_api(task_code, method, output_widget):
    payload = {
        "method": str(method),
        "reqCode": generate_id(),
        "reqTime": int(datetime.datetime.now().timestamp() * 1000),
        "taskCode": task_code
    }
    try:
        response = requests.post(HAIKANG_URL, json=payload)
        result = response.json()
        if result.get("code") == "0":
            log(output_widget, f"海康接口调用成功，method={method}")
            return True
        else:
            log(output_widget, f"海康接口调用失败: {result}")
    except Exception as e:
        log(output_widget, f"调用海康接口异常：{e}")
    return False
# =================== 业务功能函数 > 海康完成任务逻辑 =================== #

# =================== 业务功能函数 > 未来完成任务逻辑 =================== #
def call_future_api(task_code, stage, state, output_widget):
    payload = {
        "id": task_code,
        "stage": stage,
        "state": state
    }
    try:
        response = requests.post(WEILAI_URL, json=payload)
        result = response.json()
        if result.get("code") == "0":
            log(output_widget, f"未来接口调用成功，stage={stage}, state={state}")
            return True
        else:
            log(output_widget, f"未来接口调用失败: {result}")
    except Exception as e:
        log(output_widget, f"调用未来接口异常：{e}")
    return False
# =================== 业务功能函数 > 未来完成任务逻辑 =================== #

# =================== 业务功能函数 > 完成任务逻辑 =================== #
def complete_task(task_id, output_widget):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("SELECT * FROM tms_task_poll WHERE pid_code = %s", (task_id,))
    task = cursor.fetchone()
    if not task:
        log(output_widget, f"未找到任务号为 {task_id} 的容器任务")
        conn.close()
        return

    status = int(task['status'])
    task_source = str(task['task_source'])
    task_poll_id = task['id']

    cursor.execute("SELECT * FROM tms_task WHERE task_poll_id = %s", (task_poll_id,))
    car_task = cursor.fetchone()
    if not car_task:
        log(output_widget, "未找到车辆任务")
        conn.close()
        return

    task_code = car_task['task_code']

    if status >= 50:
        log(output_widget, "任务状态为已完成或更高，无需调用接口")
        conn.close()
        return

    log(output_widget, f"开始处理完成任务逻辑，task_source={task_source}, status={status}")

    if task_source == '1':  # 海康
        methods_map = {
            20: [30, 40, 50],
            30: [40, 50],
            40: [50]
        }
        for method in methods_map.get(status, []):
            if not call_haikang_api(task_code, method, output_widget):
                break

    elif task_source == '2':  # 未来
        stage_state_map = {
            20: [(0, 1), (1, 1), (3, 2)],
            30: [(1, 1), (3, 2)],
            40: [(3, 2)]
        }
        for stage, state in stage_state_map.get(status, []):
            if not call_future_api(task_code, stage, state, output_widget):
                break
    else:
        log(output_widget, f"未知的task_source: {task_source}")

    conn.close()
# =================== 业务功能函数 > 完成任务逻辑 =================== #


# =================== 标识修复功能 ===================
def flag_repair(code, output_widget):
    def log(msg):
        output_widget.config(state="normal")
        output_widget.insert(tk.END, "\n" + msg)
        output_widget.see(tk.END)  # 自动滚动到底部
        output_widget.config(state="disabled")
        output_widget.update()

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    query_loc_ids_sql = """
        SELECT
            dg.id as group_id,
            dg.depth_group_code,
            dg.storage_location_id,
            dg.depth_one,
            dg.free_flag,
            dg.sold_out_flag,
            dg.hit_the_shelf_flag,
            sl.storage_location_name,
            sl.storage_location_status
        FROM
            tms_depth_group dg
            LEFT JOIN tms_storage_location sl ON dg.storage_location_id = sl.id
        WHERE
            dg.depth_group_code = %s
    """
    cursor.execute(query_loc_ids_sql, (code,))
    result_list = cursor.fetchall()

    need_update = False
    update_depth_codes = set()

    for row in result_list:
        loc_id = row['storage_location_id']
        loc_name = row['storage_location_name']
        depth_code = row['depth_group_code']
        sold_out_flag = str(row['sold_out_flag'])
        hit_the_shelf_flag = str(row['hit_the_shelf_flag'])

        # 下架任务判断
        cursor.execute("""
            SELECT task_code FROM tms_task_poll 
            WHERE start_location_id = %s AND pid_code != '0' AND status IN (10, 20, 30, 40)
        """, (loc_id,))
        down_tasks = cursor.fetchall()
        if down_tasks:
            for task in down_tasks:
                log(f"{loc_name} 存在 下架任务，任务号: {task['task_code']}")
            if sold_out_flag != '1':
                cursor.execute("UPDATE tms_depth_group SET sold_out_flag = 1 WHERE depth_group_code = %s", (depth_code,))
                need_update = True
                update_depth_codes.add(depth_code)
        else:
            log(f"{loc_name} 不存在 下架任务")
            if sold_out_flag != '0':
                cursor.execute("UPDATE tms_depth_group SET sold_out_flag = 0 WHERE depth_group_code = %s", (depth_code,))
                need_update = True
                update_depth_codes.add(depth_code)

        # 上架任务判断
        cursor.execute("""
            SELECT task_code FROM tms_task_poll 
            WHERE end_location_id = %s AND pid_code != '0' AND status IN (10, 20, 30, 40)
        """, (loc_id,))
        up_tasks = cursor.fetchall()
        if up_tasks:
            for task in up_tasks:
                log(f"{loc_name} 存在 上架任务，任务号: {task['task_code']}")
            if hit_the_shelf_flag != '1':
                cursor.execute("UPDATE tms_depth_group SET hit_the_shelf_flag = 1 WHERE depth_group_code = %s", (depth_code,))
                need_update = True
                update_depth_codes.add(depth_code)
        else:
            log(f"{loc_name} 不存在 上架任务")
            if hit_the_shelf_flag != '0':
                cursor.execute("UPDATE tms_depth_group SET hit_the_shelf_flag = 0 WHERE depth_group_code = %s", (depth_code,))
                need_update = True
                update_depth_codes.add(depth_code)

    # 查找最小深度的储位
    if result_list:
        min_depth_row = min(result_list, key=lambda x: x['depth_one'])
        status = str(min_depth_row['storage_location_status'])
        flag = str(min_depth_row['free_flag'])
        depth_code = min_depth_row['depth_group_code']

        if status == '1' and flag != '1':
            log(f"深度L最小储位 {min_depth_row['storage_location_name']} 为 \"空闲\"")
            cursor.execute("UPDATE tms_depth_group SET free_flag = 1 WHERE depth_group_code = %s", (depth_code,))
            need_update = True
            update_depth_codes.add(depth_code)
        elif status != '1' and flag == '1':
            log(f"深度L最小储位 {min_depth_row['storage_location_name']} 不为 \"空闲\"")
            cursor.execute("UPDATE tms_depth_group SET free_flag = 0 WHERE depth_group_code = %s", (depth_code,))
            need_update = True
            update_depth_codes.add(depth_code)

    if need_update:
        if messagebox.askyesno("确认修复", "检测到深度组标识需要修复，是否立即修复？"):
            conn.commit()
            log("已修复，请重新查询")
        else:
            log("已取消修复")
    else:
        log("未检测到需要修复的内容")

    conn.close()

def query_depth_group(code, output_widget):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    query_sql = """
    SELECT
	dg.id AS '深度组ID',
	dg.depth_group_code AS '深度组编码',
	dg.depth_one AS '深度1',
	dg.depth_two AS '深度2',
CASE
		dg.single_or_double 
		WHEN 1 THEN
		'单向' 
		WHEN 2 THEN
		'双向' ELSE CONCAT( '未知方向(', dg.single_or_double, ')' ) 
	END AS '深度组方向',
	dg.hit_the_shelf_order AS '上架顺序',
	dg.sold_out_order AS '下架顺序',
CASE
		dg.direction 
		WHEN 1 THEN
		'单进单出' 
		WHEN 2 THEN
		'双进双出' 
		WHEN 3 THEN
		'一边进一边出' ELSE CONCAT( '未知方向(', dg.direction, ')' ) 
	END AS '取放货方向',
	dg.hit_the_shelf_flag AS '上架标识: 存在=1, 不存在=0',
	dg.sold_out_flag AS '下架标识: 存在=1, 不存在=0',
	dg.free_flag AS '空闲标识: 空闲=1, 不空闲=0',
	sl.storage_location_name AS '储位名称',
CASE
		sl.storage_location_status 
		WHEN 0 THEN
		'占用' 
		WHEN 1 THEN
		'空闲' 
		WHEN 2 THEN
		'预占' ELSE CONCAT( '未知状态(', sl.storage_location_status, ')' ) 
	END AS '储位状态' 
FROM
	`tms_depth_group` dg
	LEFT JOIN tms_storage_location sl ON dg.storage_location_id = sl.id 
WHERE
	dg.depth_group_code = %s
    """
    cursor.execute(query_sql, (code,))
    result_list = cursor.fetchall()
    conn.close()

    output_widget.config(state="normal")
    output_widget.delete("1.0", tk.END)

    formatted_result = json.dumps(result_list, indent=4, ensure_ascii=False, default=str)
    output_widget.insert(tk.END, formatted_result)
    output_widget.config(state="disabled")


def query_demand(demand_id, output_widget):
    # TODO: 还可以查询相关日志？？
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT remark FROM tms_demand_poll WHERE demand_id = %s", (demand_id,))
    row = cursor.fetchone()
    conn.close()

    output_widget.config(state="normal")
    output_widget.delete("1.0", tk.END)
    output_widget.insert(tk.END, row[0] if row else "未找到需求")
    output_widget.config(state="disabled")


# =================== 主界面 =================== #
class App(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title("TMS 数据修复工具")
        self.geometry("900x600")

        notebook = ttk.Notebook(self)
        notebook.pack(expand=True, fill='both')

        self.task_tab(notebook)
        self.depth_tab(notebook)
        self.demand_tab(notebook)

    def task_tab(self, notebook):
        frame = ttk.Frame(notebook)
        notebook.add(frame, text="任务")

        frame.rowconfigure(3, weight=1)
        for i in range(3):
            frame.columnconfigure(i, weight=1)

        tk.Label(frame, text="任务号:").grid(row=0, column=0, sticky="w")
        task_entry = tk.Entry(frame)
        task_entry.grid(row=0, column=1, columnspan=2, sticky="ew")

        output = create_output_with_scrollbar(frame, row=3, columnspan=3)

        tk.Button(frame, text="查询", command=lambda: query_task(task_entry.get(), output)).grid(row=1, column=0)
        tk.Button(frame, text="取消任务", command=lambda: cancel_task(task_entry.get(), output)).grid(row=1, column=1)
        tk.Button(frame, text="完成任务", command=lambda: complete_task(task_entry.get(), output)).grid(row=1, column=2)

    def depth_tab(self, notebook):
        frame = ttk.Frame(notebook)
        notebook.add(frame, text="深度组")

        frame.rowconfigure(2, weight=1)
        frame.columnconfigure(0, weight=1)
        frame.columnconfigure(1, weight=1)

        tk.Label(frame, text="深度组编码:").grid(row=0, column=0, sticky="w")
        group_entry = tk.Entry(frame)
        group_entry.grid(row=0, column=1, sticky="ew")

        output = create_output_with_scrollbar(frame, row=2, columnspan=2)

        tk.Button(frame, text="查询", command=lambda: query_depth_group(group_entry.get(), output)).grid(row=1, column=0)
        tk.Button(frame, text="标识修复", command=lambda: flag_repair(group_entry.get(), output)).grid(row=1, column=1)

    def demand_tab(self, notebook):
        frame = ttk.Frame(notebook)
        notebook.add(frame, text="需求")

        frame.rowconfigure(2, weight=1)
        frame.columnconfigure(0, weight=1)
        frame.columnconfigure(1, weight=1)

        tk.Label(frame, text="需求ID:").grid(row=0, column=0, sticky="w")
        demand_entry = tk.Entry(frame)
        demand_entry.grid(row=0, column=1, sticky="ew")

        output = create_output_with_scrollbar(frame, row=2, columnspan=2)

        tk.Button(frame, text="查询", command=lambda: query_demand(demand_entry.get(), output)).grid(row=1, column=0)
# =================== 主界面 =================== #


# =================== 启动 =================== #
if __name__ == "__main__":
    app = App()
    app.mainloop()
# =================== 启动 =================== #